import pymysql
from loguru import logger


class AmazonMysqlPipeline:

    def open_spider(self, spider):
        self.db = pymysql.connect(host="localhost", user="root", password="root", db="py_spider")
        self.cursor = self.db.cursor()
        # 建表
        sql = """
        create table if not exists tb_amazon (
            id    int not null primary key auto_increment,
            title varchar(255),
            price varchar(255),
            url   varchar(500)
        ) engine = innodb;
        """
        if self.cursor.execute("show tables like 'tb_amazon'"):
            print("表已存在!")
        else:
            try:
                self.cursor.execute(sql)
                logger.debug(f"建表成功")
            except Exception as e:
                logger.exception(f"建表失败,{type(e)}, {e}")

    def process_item(self, item, spider):
        logger.info(f"数据:{item}插入中...")
        sql = "insert into tb_amazon(title, price, url) values(%s,%s,%s)"
        try:
            self.cursor.execute(sql, (item['title'], item['price'], item['url']))
            logger.info(f"数据插入成功!!!")
            self.db.commit()
        except Exception as e:
            logger.exception(f"数据插入失败,{type(e)}, {e}")
            self.db.rollback()
        return item

    def close_spider(self, spider):
        self.cursor.close()
        self.db.close()